DATABASE MANAGEMENT
SYSTEM
THEORY AND IDEAS
25-04-2019 1
25-04-2019 2
Information is not useful if not organized
In database, data are organized in a way that people find meaningful
and useful.
Database Management System (DBMS) is used to input, sort, organize
and store data.
A collection of related data. It should support
Definition
Construction
Manipulation
Database Management System: A collection of programs that enable the
users to create and maintain a database
DATABASE MANAGEMENT SYSTEM
DATABASE
MANAGEME
NT SYSTEM
4/25/2019 3
DATABASE
MANAGEME
NT SYSTEM
4/25/2019 4
DATABASE
MANAGEMENT SYSTEM
Data storage, retrieval, and update: The
ability to store, retrieve, and update the
data that are in the database.
User-accessible catalog: where
descriptions of database components are
stored and are accessible to the users
Shared update support: A mechanism to
ensure accuracy when several users are
updating the database at the same time
Backup and Recovery Services:
Mechanisms for recovering the database
in the event that a database is damaged
somehow.
Security Services: Mechanisms to ensure
that certain rules are followed with regard
to data in the database and any changes
that are made in the data
4/25/2019 5
DATABASE
MANAGEMENT
SYSTEM
5. Integrity services: Mechanisms to
ensure that certain rules are
followed with regard to data in the
database and any changes that are
made in the data.
6. Data Independence: Facilities to
support the independence of
programs from the structure of the
database.
7. Replication support: A facility to
manage copies of the same data at
multiple locations.
8. Utility Services: DBMS provided
services that assist in the general
maintenance of the database.
SHARED UPDATES
Multiple users are making updates to the
database at the same time.
Problem:
Multiple people updating the
database simultaneously can override
each other
Example:
Agents T1 & T2 simultaneously read
the seats reserved on Flight 890 i.e.
80
T1 cancels 5 seats updating the seats
reserved on Flight 890 to 75
T2 reserves 4 additional seats on the
flight and updates the seats reserved
on Flight 890 to 84.
If T1 updates the database before T2.
T2 will override T1’s change and make
reservations to 84 rather than getting
the correct value of 79.
Similarly if T2 updates before T1 the
seats reserved will be 75
SHARED UPDATES SOLUTION : Batch processing |
Locking
6
DATABASE
MANAGEMENT SYSTEM
Two phase lock ---
Required when multiple records are updated as a
result of a user action (e.g. filling form etc.)
All the records accessed are locked progressively
till the required updates are completed
Growing Phase: More and more locks are
added without releasing locks
After all locks are placed the database is
updated
Shrinking Phase: All locks are removed and
no new ones are added
7
DATABASE
MANAGEMENT
SYSTEM
4/25/2019 8
DATABASE
MANAGEMENT
SYSTEM
4/25/2019 9
Security
Protection against unauthorized access: either
intentional or accidental.
Three main features for protection
Passwords: Allows only authorized users to access the
database. Access privileges can be provided based on
access needs
Encryption: Encodes data to non-decipherable. Data
decoded on demand to prevent hackers from accessing
data
Views: Different snapshot of the data ensures that users
only get access to data they need
Integrity
Integrity Constraints are the conditions that data
must satisfy during initial input & updates.
There are four categories of constraints
Data Type
Legal Values
Format
Key Constraints
Entity Integrity Constraints (Primary Key)
Enforces the uniqueness of the primary key
Referential Integrity Constraints (Foreign Key)
Value of foreign key must match the value of primary key for some row
in another table
Integrity: Solutions
Ignore constraint
Undesirable as it can lead to inconsistent data
Let user enforce the constraint
Undesirable since user mistakes can be disastrous
Let programmer build the logic of constraints in
the programs
Makes programs complex: harder to write, harder to
maintain, and expensive
Place burden on the DBMS.
Preferred way: Cost of DBMS development amortized
over large user base, hence economical
Replication
Duplication of data at multiple physical locations
Each replica of the data can be changed
independently
Periodically the replicas update their data to the
master database this process is called
synchronization
Disaster Planning: Backup & Recovery
Database can be damaged in a number of ways
Power outage, disk crashes, floods, user errors
Periodic backups limit the loss due to sudden failures
Data can be recovered from the latest backup and
the changes since the backup need to be done in
either of two ways
Manually
From a catalog (if exists) recording all updates to the
database since the last backup.
Catalog/Data Dictionary
Contains information describing the database
Schema for the database
Characteristic for each field
Possible values for each field
Description of the data
Relationships
Description of the programs
Data Dictionary is same as catalog but may contain
wider set of information than catalog
Hierarchical model
Data are organized in an upside down tree
Each entity has one parent and many children
Old and not used now
Network model
Entities are organized in a graph
Entities can be accessed through several paths
Old and not used
Relational model
Data are organized in two dimensional tables (relations)
Tables re related to each other
Relational Database Management System (RDBMS) are
more common model used today
Relation (Name, Attributes, Tuples)
Relation appears in 2 dimensional table
That doesnt mean data stored as table; the physical
storage of data is independent of the logical
organization of data
Attributes are the column
heading
Each column must have a
unique heading
Number of columns is
called the degree of the
relation
Tuple is a collection of
attribute value
Total number of rows
is called Cardinality of
the relation
Each relation must have a
unique name
Name
OPERATIONS
ON
RELATIONS
Insert operation
Unary operation
Insert Operation: Inserts new tuple into the relation
Delete operation
Unary operation
Delete Operation: Deletes tuple from the relation
Update operation
Unary operation
Update Operation: Changes the values of some attributes
of a tulpe
Select operation
Unary operation
Select Operation: Uses some criteria to select
some tuples from the original relation
Project operation
Unary operation
Project Operation: Creates relation in
which each tulpe has fewer attributes
Join operation
Binary operation
Join Operation: Takes two relation and combine them
based on common attribute
Union operation
Binary operation
Union Operation: Creates new relation in which each tuple
is either in the first relation, the second relation or in both
Intersection operation
Binary operation
Intersection Operation: Creates new relation in
which each tuple is either in both relations.
Difference operation
Binary Operation
Difference Operation: Creates new relation where the
new tuples are in the first relation but not in the second.
STRUCTURED
QUERY
LANGUAGE
SQL
SQL is the standard language used for relational databases.
It is declarative language where users declare what they want without
having to write a step by stem procedure.
It was first implemented by Oracle Corporation
1. Insert
SQL Insert Operation format
insert into RELATION-NAME
values (, , )
Insert (Example)
insert into COURSES
values (CIS52, TCP/IP Protocols, 6)
2. Delete
SQL Delete Operation format
delete from RELATION-NAME
where criteria
Delete (Example)
Delete from COURSES
where No = CIS19
3. Update
SQL Update Operation format
update RELATION-NAME
set attribute1 = value1 attribute 2 = value2
where criteria
Update (Example)
update COURSES
set unit = 6
where No = CIS51
4. Select
SQL Select Operation format
select *
from RELATION-NAME
where criteria
Select (Example)
select *
from COURSES
where Unit = 5
5. Project
SQL Project Operation format
select attribute-list
from RELATION-NAME
Project (Example)
select No, Unit
from COURSES
6. Join
SQL Join Operation format
select attribute-list
from RELATION NO1, RELATION NO2
where criteria
Join (Example)
select No, Course-Name, Unit, Professor
from COURSES, TAUGHT-BY
where COURSES.No = TAUGHT-BY.No;
7. Union
SQL Union Operation format
select *
from RELATION NO1
union
select *
from RELATION NO2
Union (Example)
select *
from CIS15-Roster
union
select *
from CIS52-Roster;
8. Intersection
SQL Intersection Operation format
select *
from RELATION NO1
intersection
select *
from RELATION NO2
Intersection
(Example)
select *
from CIS15-Roster
intersection
select *
from CIS52-Roster;
9. Difference
SQL Difference Operation format
select *
from RELATION NO1
minus
select *
from RELATION NO2
Intersection
(Example)
select *
from CIS15-Roster
minus
select *
from CIS52-Roster;
OTHER
DATABASE
MODELS
Distributed Databases
Based on the
relational model
Data are stored in
several computers
that communicate
through the internet
Data are either (1)
fragmented or (2)
replicated
1. Fragmented
Distributed
Databases
Data are localized; data are stored on local site
Data can be accessed from different sites as
well as the local site
Although each site has complete control over its
local data, there is global control through the
internet
2. Replicated Distributed Database
Each site holds an
exact replica of
another site
Any modification in
one site is repeated
at every site
If the system fails in
one site, the users at
this site can access
data at another site
The levels of Data
One or
more tables
Database
A collection of Records
Table
(relation)
A
group of related fields
Record
One
or more character
Field
At least
8 bits
Character
0
or 1
Bit
Types of Database Program
Are software tools
used to create
database. Examples of
database programs are:
File Management
Programs
Database Management
System
Advanced Databases
Data Warehouses and Data Mining
A combination of smaller database that belong to the corporation
Client/Server Database Systems
Software programs used to respond to remote usersrequest for
information over the LAN. Server software runs on the server. Client
software connects the user to server. The front end of the software is
the part that interact with users. The back end refers to the program
code. To request information query language is used.
Web-database integration
Enables customers to proceed enquires over the net. Users dont have
to use SQL. The web server accept the request and translates it to a
query that is sent to the database over the internet
5 Characteristics of Good Database
ENSURING DATA IS VALIDDATA INTEGRITY
Data is separated from softwareData Independence
Repetition of input data is avoided
Avoiding data Redundancy
Data is not accessible to unauthorized usersData Security
Set procedures for adding ,deleting records for the purpose of
optimization
Data Maintenance